﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Diagnostics;
using System.Reflection;
using ChuanPlus.DAO;
using ChuanPlus.Utils;
using System.Configuration;
using System.IO;

namespace ChuanPlus.Utils
{
    class clsReport
    {
        private string[] m_strColArr = new string[256];
        private clsExcelBase m_clsExcelBase = new clsExcelBase();
        Microsoft.Office.Interop.Excel.Application excelApp;
        string ExportPath;
        public clsReport()
		{
			m_strColArr = m_clsExcelBase.GetColArr();
            AppSettingsReader config = new AppSettingsReader();            
            ExportPath = ((string)(config.GetValue("ExportPath", typeof(string))));
		}
        
        
        public string ExportSaleReportToExcel(string title, System.Data.DataTable datatable, int column)
        {            
            string strTempPath2, strTempPath, strPath;
            string strTemp, strtmpDate, strtmpTime;
            Process[] preExcelProcesses = null;

            Workbook excelBook;
            Worksheet excelSheet;
            Range excelRange;
            int m_intRowHeader = 4;
            try
            {                
                // =========== COPY FILE TEMPLATE ======================
                strTemp = System.Windows.Forms.Application.StartupPath + "\\Template\\" + title + ".xls";
                strtmpDate = DateTime.Now.Year.ToString("####") + DateTime.Now.Month.ToString("0#") + DateTime.Now.Day.ToString("0#");
                strtmpTime = DateTime.Now.Hour.ToString("0#") + DateTime.Now.Minute.ToString("0#") + DateTime.Now.Second.ToString("0#");
                strPath = ExportPath;
                strPath += title;
                strTempPath2 = strPath;

                if (Directory.Exists(strPath) == false)
                    Directory.CreateDirectory(strPath);

                strTempPath = strPath;
                strPath += "\\" + title;
                strPath = strPath + "_" + strtmpDate + "-" + strtmpTime + ".xls";

                File.Copy(strTemp, strPath, true);

                // ============== MO FILE EXCEL =========================
                System.Windows.Forms.Application.DoEvents();
                preExcelProcesses = Process.GetProcessesByName("EXCEL");
                excelApp = new Microsoft.Office.Interop.Excel.Application();
                excelApp.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(excelApp_WorkbookBeforeClose);
                excelBook = excelApp.Workbooks.Open(strPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excelSheet = (Worksheet)excelBook.Worksheets.get_Item(1);

                // =========== DUA DU LIEU VAO ==========================          
                if (title.Equals("Sale Report"))
                    excelSheet.Cells[2, column] = DateTime.Now.ToString("dd/MMM/yyyy");
                else
                    excelSheet.Cells[2, column + 1] = DateTime.Now.ToString("dd/MMM/yyyy");

                for (int i = 0; i < datatable.Rows.Count; i++)
                {                    
                    for (int k = 1; k <= column; k++ )
                    {
                        excelRange = excelSheet.get_Range(m_strColArr[k] + (m_intRowHeader + i + 1), Missing.Value);
                        excelRange.Value2 = datatable.Rows[i][k-1].ToString();
                        m_clsExcelBase.FormatRange(excelRange, false, true);
                    }                    
                }
                
                // ================ SAVE FILE ============================
                excelBook.Save();
                excelApp.Visible = true;
                
            }
            catch (Exception ex)
            {
                m_clsExcelBase.KillProcess(preExcelProcesses);
                throw ex;
            }
            return strTempPath2;
        }
        //public string ExportProductionReport(string title, System.Data.DataTable datatable, int column)
        //{
        //    string strTempPath2, strTempPath, strPath;
        //    string strTemp, strtmpDate, strtmpTime;
        //    Process[] preExcelProcesses = null;

        //    Workbook excelBook;
        //    Worksheet excelSheet;
        //    Range excelRange;
            
        //    try
        //    {
        //        // =========== COPY FILE TEMPLATE ======================
        //        strTemp = System.Windows.Forms.Application.StartupPath + "\\Template\\" + title + ".xls";
        //        strtmpDate = DateTime.Now.Year.ToString("####") + DateTime.Now.Month.ToString("0#") + DateTime.Now.Day.ToString("0#");
        //        strtmpTime = DateTime.Now.Hour.ToString("0#") + DateTime.Now.Minute.ToString("0#") + DateTime.Now.Second.ToString("0#");
        //        strPath = System.IO.Directory.GetCurrentDirectory().Replace("bin\\Debug", "");
        //        strPath += title;
        //        strTempPath2 = strPath;

        //        if (Directory.Exists(strPath) == false)
        //            Directory.CreateDirectory(strPath);

        //        strTempPath = strPath;
        //        strPath += "\\" + title;
        //        strPath = strPath + "_" + strtmpDate + "-" + strtmpTime + ".xls";

        //        File.Copy(strTemp, strPath, true);

        //        // ============== MO FILE EXCEL =========================
        //        System.Windows.Forms.Application.DoEvents();
        //        preExcelProcesses = Process.GetProcessesByName("EXCEL");
        //        excelApp = new Microsoft.Office.Interop.Excel.Application();
        //        excelBook = excelApp.Workbooks.Open(strPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        //                    Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        //        excelSheet = (Worksheet)excelBook.Worksheets.get_Item(1);

        //        // =========== DUA DU LIEU VAO ==========================          

        //        excelSheet.Cells[4, 2] = "Ngày in: " + DateTime.Now.ToString("dd/MM/yyyy");
        //        excelSheet.Cells[5, 3] = datatable.Rows[0]["product"].ToString();
        //        excelSheet.Cells[5, 5] = datatable.Rows[0]["DATE"].ToString();
        //        m_intRowHeader = 8;
        //        //==== FORMAT DONG SAU CODE NO =============================================================
        //            excelRange = excelSheet.get_Range(m_strColArr[1] + (8), m_strColArr[2] + (8));                    
        //            m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, false);
        //            excelRange = excelSheet.get_Range(m_strColArr[2] + (8), m_strColArr[3] + (8));
        //            m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, false);
                    


        //            excelRange = excelSheet.get_Range(m_strColArr[4] + (8), m_strColArr[6] + (8));                
        //            m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, false);
    
        //        for (int i = 0; i < datatable.Rows.Count; i++)
        //        {
        //            for (int k = 1; k <= column; k++)
        //            {
        //                if (k == 4)
        //                {
        //                    excelRange = excelSheet.get_Range(m_strColArr[4] + (m_intRowHeader + i + 1), m_strColArr[6] + (9));
        //                    excelRange.Value2 = "";

        //                    m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, false);
        //                }                            
        //                else
        //                {
        //                    excelRange = excelSheet.get_Range(m_strColArr[k] + (m_intRowHeader + i + 1), Missing.Value);
        //                    excelRange.Value2 = datatable.Rows[i][k - 1].ToString();
        //                    //m_clsExcelBase.FormatRange(excelRange, false, true);
        //                    m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, false);
        //                }                       
        //            }                    
        //        }
        //        //excelRange = excelSheet.get_Range(m_strColArr[4] + (9), m_strColArr[6] + (12));
        //        excelRange = excelSheet.get_Range(m_strColArr[4] + (9), m_strColArr[6] + (9 + datatable.Rows.Count));
        //        excelSheet.get_Range(m_strColArr[4] + (9), m_strColArr[6] + (9 + datatable.Rows.Count)).MergeCells = true;
        //        m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, false);
        //        excelSheet.get_Range(m_strColArr[4] + (9), m_strColArr[6] + (9 + datatable.Rows.Count)).VerticalAlignment = XlVAlign.xlVAlignTop;
        //        excelSheet.get_Range(m_strColArr[4] + (9), m_strColArr[6] + (9 + datatable.Rows.Count)).HorizontalAlignment = XlHAlign.xlHAlignLeft;
        //        excelSheet.get_Range(m_strColArr[1] + (9), m_strColArr[6] + (9 + datatable.Rows.Count)).WrapText = true;
        //        excelRange.Value2 = datatable.Rows[0][3].ToString();

                
        //        // ============ FOOTER ===================================
        //            m_intRowHeader += datatable.Rows.Count + 1;
        //            excelRange = excelSheet.get_Range(m_strColArr[1] + (m_intRowHeader), m_strColArr[2] + (m_intRowHeader));
        //            excelSheet.get_Range(m_strColArr[1] + (m_intRowHeader), m_strColArr[2] + (m_intRowHeader)).MergeCells = true;
        //            excelRange.Value2 = "TOTAL                                                                        Tổng Cộng ";
        //            m_clsExcelBase.FormatRange(excelRange, false, MyColor.White, MyColor.Black, true, true);
        //            excelSheet.get_Range(m_strColArr[1] + (m_intRowHeader), m_strColArr[2] + (m_intRowHeader)).WrapText = true;
        //            excelSheet.get_Range(m_strColArr[1] + (m_intRowHeader), m_strColArr[2] + (m_intRowHeader)).RowHeight = 38;
        //            excelSheet.get_Range(m_strColArr[1] + (m_intRowHeader), m_strColArr[2] + (m_intRowHeader)).VerticalAlignment = XlVAlign.xlVAlignTop;

        //            excelRange = excelSheet.get_Range(m_strColArr[3] + (m_intRowHeader), Missing.Value);
        //            excelRange.Value2 = datatable.Rows[0]["TotalUsed"].ToString();
        //            m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, false);

        //            excelRange = excelSheet.get_Range(m_strColArr[4] + (m_intRowHeader), m_strColArr[6] + (m_intRowHeader));
        //            excelRange.Value2 = "";
        //            m_clsExcelBase.FormatRange(excelRange, false, MyColor.White, MyColor.Black, true, false);
        //        // ============ FOOTER 2 ====================================
        //            excelRange = excelSheet.get_Range(m_strColArr[1] + (m_intRowHeader + 1), Missing.Value);
        //            excelRange.Value2 = "PACKING                 Loại thùng";
        //            m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, true);
        //            excelSheet.get_Range(m_strColArr[1] + (m_intRowHeader + 1), Missing.Value).VerticalAlignment = XlVAlign.xlVAlignTop;
        //            excelSheet.get_Range(m_strColArr[1] + (m_intRowHeader + 1), Missing.Value).WrapText = true;

        //            excelRange = excelSheet.get_Range(m_strColArr[2] + (m_intRowHeader + 1), Missing.Value);
        //            excelRange.Value2 = "BALANCCE                Chưa đóng gói";
        //            m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, true);
        //            excelSheet.get_Range(m_strColArr[2] + (m_intRowHeader + 1), Missing.Value).VerticalAlignment = XlVAlign.xlVAlignTop;
        //            excelSheet.get_Range(m_strColArr[2] + (m_intRowHeader + 1), Missing.Value).WrapText = true;
    
        //            excelRange = excelSheet.get_Range(m_strColArr[3] + (m_intRowHeader + 1), Missing.Value);
        //            excelRange.Value2 = "LOSS                    NVL hao hụt";
        //            m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, true);
        //            excelSheet.get_Range(m_strColArr[3] + (m_intRowHeader + 1), Missing.Value).VerticalAlignment = XlVAlign.xlVAlignTop;
        //            excelSheet.get_Range(m_strColArr[3] + (m_intRowHeader + 1), Missing.Value).WrapText = true;
                    

        //            excelRange = excelSheet.get_Range(m_strColArr[4] + (m_intRowHeader + 1), m_strColArr[6] + (m_intRowHeader + 1));
        //            m_clsExcelBase.FormatRange(excelRange, false, MyColor.White, MyColor.Black, true, true);
        //            excelSheet.get_Range(m_strColArr[4] + (m_intRowHeader + 1), Missing.Value).RowHeight = 55;
        //            excelSheet.get_Range(m_strColArr[4] + (m_intRowHeader + 1), Missing.Value).VerticalAlignment = XlVAlign.xlVAlignTop;
        //            excelSheet.Cells[m_intRowHeader + 1, 5] = "FACTORY MANAGER";
                    

        //            excelRange = excelSheet.get_Range(m_strColArr[1] + (m_intRowHeader + 2), Missing.Value);
        //            excelRange.Value2 = datatable.Rows[0]["Packing"].ToString();
        //            m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, false);

        //            excelRange = excelSheet.get_Range(m_strColArr[2] + (m_intRowHeader + 2), Missing.Value);
        //            excelRange.Value2 = datatable.Rows[0]["Balance"].ToString();
        //            m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, false);

        //            excelRange = excelSheet.get_Range(m_strColArr[3] + (m_intRowHeader + 2), Missing.Value);
        //            excelRange.Value2 = datatable.Rows[0]["loss"].ToString();
        //            m_clsExcelBase.FormatRange(excelRange, true, MyColor.White, MyColor.Black, true, false);

        //            excelRange = excelSheet.get_Range(m_strColArr[4] + (m_intRowHeader + 2), m_strColArr[6] + (m_intRowHeader + 2));
        //            m_clsExcelBase.FormatRange(excelRange, false, MyColor.White, MyColor.Black, true, true);
        //            excelSheet.get_Range(m_strColArr[4] + (m_intRowHeader + 2), Missing.Value).RowHeight = 55;
        //            excelSheet.get_Range(m_strColArr[4] + (m_intRowHeader + 2), Missing.Value).VerticalAlignment = XlVAlign.xlVAlignTop;
        //            excelSheet.get_Range(m_strColArr[4] + (m_intRowHeader + 2), Missing.Value).HorizontalAlignment = XlHAlign.xlHAlignLeft;
        //            excelSheet.Cells[m_intRowHeader + 2, 5] = "OPERATOR";                    


        //        // ================ SAVE FILE ============================
        //        excelBook.Save();
        //        excelApp.DisplayAlerts = false;
        //        excelApp.UserControl = true;
        //        excelBook.Close(null, null, null);
        //    }
        //    catch (Exception ex)
        //    {
        //        m_clsExcelBase.KillProcess(preExcelProcesses);
        //        return ex.Message;
        //    }
        //    return strTempPath2;
        //}

        public void ExportDebtorReport(System.Data.DataTable dtDebtor, DateTime fromDate, DateTime toDate, Customer cus, double OpenningBalance)
        {
            Process[] preExcelProcesses = null;
            string strTemp, strtmpDate, strtmpTime, strPath, strTempPath;
            Workbook excelBook;
            Worksheet excelSheet;
            Range excelRange;
            int iRowHeader = 8;     // the number of header row in excel file     
            int columns = 8;        // the number of columns in excel file
            try
            {
                strTemp = System.Windows.Forms.Application.StartupPath + "\\Template\\DebtorReport.xls";
                strtmpDate = DateTime.Now.Year.ToString("####") + DateTime.Now.Month.ToString("0#") + DateTime.Now.Day.ToString("0#");
                strtmpTime = DateTime.Now.Hour.ToString("0#") + DateTime.Now.Minute.ToString("0#") + DateTime.Now.Second.ToString("0#");
                strPath = ExportPath;
                strPath += "DebtorReport";
                if (Directory.Exists(strPath) == false)
                    Directory.CreateDirectory(strPath);

                strTempPath = strPath;
                strPath += "\\" + "Debtor Report";
                strPath = strPath + "_" + strtmpDate + "-" + strtmpTime + ".xls";

                File.Copy(strTemp, strPath, true);
                // ============== Open EXCEL =========================
                System.Windows.Forms.Application.DoEvents();
                preExcelProcesses = Process.GetProcessesByName("EXCEL");
                excelApp = new Microsoft.Office.Interop.Excel.Application();
                excelApp.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(excelApp_WorkbookBeforeClose);
                excelBook = excelApp.Workbooks.Open(strPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excelSheet = (Worksheet)excelBook.Worksheets.get_Item(1);
                ///----------------Bind data------------------------------////
                ///         bind customer info  ////
                #region Customer info
                excelRange = excelSheet.get_Range(m_strColArr[1] + 2, Missing.Value);
                excelRange.Value2 = cus.CustomerName;

                excelRange = excelSheet.get_Range(m_strColArr[1] + 3, Missing.Value);
                excelRange.Value2 = cus.CustomerAdd;

                excelRange = excelSheet.get_Range(m_strColArr[1] + 4, Missing.Value);
                excelRange.Value2 = cus.CustomerPhone;

                excelRange = excelSheet.get_Range(m_strColArr[1] + 5, Missing.Value);
                excelRange.Value2 = cus.CustomerFax;
                #endregion
                // other info
                #region Other info
                excelRange = excelSheet.get_Range(m_strColArr[6] + 2, Missing.Value);
                excelRange.Value2 = fromDate;
                excelRange = excelSheet.get_Range(m_strColArr[6] + 3, Missing.Value);
                excelRange.Value2 = toDate;

                excelRange = excelSheet.get_Range(m_strColArr[1] + 8, Missing.Value);
                excelRange.Value2 = "Balance to " + fromDate.AddDays(-1).ToString("dd/MMM/yyyy");

                excelRange = excelSheet.get_Range(m_strColArr[6] + 8, Missing.Value);
                excelRange.Value2 = OpenningBalance;
                #endregion
                for (int i = 0; i < dtDebtor.Rows.Count; i++)
                {
                    for (int k = 0; k < columns; k++)
                    {
                        excelRange = excelSheet.get_Range(m_strColArr[k] + (iRowHeader + i + 1), Missing.Value);
                        excelRange.Value2 = dtDebtor.Rows[i][k].ToString();
                        m_clsExcelBase.FormatRange(excelRange, false, true);
                    }                     
                }
                    /***************************************************************/
                    excelBook.Save();
                excelApp.Visible = true;
            }
            catch (Exception ex)
            {
                m_clsExcelBase.KillProcess(preExcelProcesses);
                throw ex;
            }
        }
        public void ExportCollectionReport(System.Data.DataTable dtCollection, DateTime fromDate, DateTime toDate)
        { 
            Process[] preExcelProcesses = null;
            string strTemp, strtmpDate, strtmpTime, strPath, strTempPath;
            Workbook excelBook;
            Worksheet excelSheet;
            Range excelRange;
            int iRowHeader = 6;     // the number of header row in excel file     
            int columns = 10;        // the number of columns in excel file
            try
            {
                // excel template file path
                strTemp = System.Windows.Forms.Application.StartupPath + "\\Template\\CollectionReport.xls";
                //get system date time and add them to new excel file name
                strtmpDate = DateTime.Now.Year.ToString("####") + DateTime.Now.Month.ToString("0#") + DateTime.Now.Day.ToString("0#");
                strtmpTime = DateTime.Now.Hour.ToString("0#") + DateTime.Now.Minute.ToString("0#") + DateTime.Now.Second.ToString("0#");
                strPath = ExportPath;
                strPath += "CollectionReport";
                if (Directory.Exists(strPath) == false)
                    Directory.CreateDirectory(strPath);

                strTempPath = strPath;
                strPath += "\\" + "Collection Report";
                strPath = strPath + "_" + strtmpDate + "-" + strtmpTime + ".xls";

                File.Copy(strTemp, strPath, true);
                // ============== Open EXCEL =========================
                System.Windows.Forms.Application.DoEvents();
                preExcelProcesses = Process.GetProcessesByName("EXCEL");
                excelApp = new Microsoft.Office.Interop.Excel.Application();
                excelApp.WorkbookBeforeClose += new AppEvents_WorkbookBeforeCloseEventHandler(excelApp_WorkbookBeforeClose);
                excelBook = excelApp.Workbooks.Open(strPath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                            Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                excelSheet = (Worksheet)excelBook.Worksheets.get_Item(1);

                #region Report date info
                excelRange = excelSheet.get_Range(m_strColArr[1] + 3, Missing.Value);
                excelRange.Value2 = fromDate;
                excelRange = excelSheet.get_Range(m_strColArr[1] + 4, Missing.Value);
                excelRange.Value2 = toDate;                
                #endregion
                for (int i = 0; i < dtCollection.Rows.Count; i++)
                {
                    for (int k = 0; k < columns; k++)
                    {
                        excelRange = excelSheet.get_Range(m_strColArr[k] + (iRowHeader + i + 1), Missing.Value);
                        excelRange.Value2 = dtCollection.Rows[i][k].ToString();
                        m_clsExcelBase.FormatRange(excelRange, false, true);
                    }
                }
                /***************************************************************/
                excelBook.Save();
                excelApp.Visible = true;
            }
            catch (Exception ex)
            {
                m_clsExcelBase.KillProcess(preExcelProcesses);
                throw ex;
            }
        }
        private void excelApp_WorkbookBeforeClose(Workbook Wb, ref bool Cancel)
        {
            excelApp.Quit();
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);
            excelApp = null;
            System.GC.Collect();
        }
    }
}
